package com.jysz.certificate.service.cm;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.annotation.Resource;

import org.hibernate.Criteria;
import org.hibernate.criterion.MatchMode;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.ProjectionList;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.springframework.stereotype.Service;

import com.jysz.certificate.persistence.dao.cm.CertificationCoreDao;
import com.jysz.certificate.persistence.dao.cm.ReleaseDao;
import com.jysz.certificate.persistence.dao.cs.tn.TheNetworkDao;
import com.jysz.certificate.persistence.dao.cs.tn.TheNetworkListDao;
import com.jysz.certificate.persistence.dao.system.UserDao;
import com.jysz.certificate.persistence.enums.CertificationType;
import com.jysz.certificate.persistence.enums.CopyOfCertificationImporting;
import com.jysz.certificate.persistence.enums.ReleaseState;
import com.jysz.certificate.persistence.enums.WorkflowType;
import com.jysz.certificate.persistence.enums.mytask.Prwarningtype;
import com.jysz.certificate.persistence.model.cm.Certificate;
import com.jysz.certificate.persistence.model.cm.Certification;
import com.jysz.certificate.persistence.model.cm.CertificationDTO;
import com.jysz.certificate.persistence.model.cm.DeleteCertificationRecord;
import com.jysz.certificate.persistence.model.cm.Release;
import com.jysz.certificate.persistence.model.cs.CheckStock;
import com.jysz.certificate.persistence.model.cs.CheckStockOption;
import com.jysz.certificate.persistence.model.cs.TheNetwork;
import com.jysz.certificate.persistence.model.cs.TheNetworkList;
import com.jysz.certificate.persistence.model.mytask.Prewarning;
import com.jysz.certificate.persistence.model.system.Brand;
import com.jysz.certificate.persistence.model.system.Financial;
import com.jysz.certificate.persistence.model.system.Shop;
import com.jysz.certificate.persistence.model.system.User;
import com.jysz.certificate.service.cs.CheckStockOptionService;
import com.jysz.certificate.service.cs.CheckStockService;
import com.jysz.certificate.service.cs.TheNetworkService;
import com.jysz.certificate.service.workflow.WorkflowService;
import com.jysz.certificate.utils.CommonUtil;
import com.jysz.certificate.utils.DateUtil;
import com.jysz.webcore.ssh.orm.hibernate.support.Page;
import com.jysz.webcore.ssh.service.EntityService;
import com.jysz.webcore.utils.SshBeanUtils;

@Service
public class CertificationService  extends EntityService<Certification,Long,CertificationCoreDao>{

	@Resource
	public CertificationCoreDao certificationDao;
	
	@Resource
	public TheNetworkListDao theNetworkListDao;
	@Resource
	public ReleaseDao rsDao;
	@Resource
	public UserDao userDao;
	@Resource
	public ReleaseDao releaseDao;
	@Resource
	public TheNetworkDao theNetworkDao;
	@Resource
	private WorkflowService wfService;
	@Resource
	private TheNetworkService theNetworkService;
	@Resource
	private ReleaseService releaseService;
	@Resource
	private  CertificateService certificateService;
	@Resource
	private CheckStockOptionService checkStockOptionService;
	@Resource
	private CheckStockService checkStockService;
	@Resource
	private CertificateRenewService certificateRenewService;
	@Resource
	private DeleteCertificationRecordService deleteCertificationRecordService;
	@Override
	public CertificationCoreDao getEntityDao() {
		return certificationDao;
	}
	
	public Certification getCertification(Long id){
		return this.find(id);
	}
	
	public Certification certificationChassisKey(String chassis){
		return certificationDao.certificationChassisKey(chassis);
	}
	/**
	 * 返回暂存 ，在途，待审核，未通过 的所有合格证
	 * @param chassis
	 * @return
	 */
	public Map<String, Certification> certificationChassisEq(List<String> chassises){
		return certificationDao.certificationMap(chassises);
	}
	/**
	 * 返回 非 暂存 ，在途，待审核，未通过 的所有合格证
	 * @param chassises 
	 * @return
	 */
	public Map<String, Certification> certificationMapNot(List<String> chassises){
		return certificationDao.certificationMapNot(chassises);
	}
	/**
	 * 合格证号
	 * @param certificationIds 
	 * @return
	 */
	public Map<String, Certification> certificationNumMapNot(List<String> certificationIds){
		return certificationDao.certificationNumMapNot(certificationIds);
	}

	/**
	 * 合格证查询
	 * @author xuxiaolong
	 * @param cf 查询对象 
	 * @param pageNo
	 * @param pageSize
	 * @return
	 */
	public Page<Certification> findPage(CertificationDTO cf,int pageNo, int pageSize){
		Criteria criteria=getEntityDao().createCriteria();
		
		if(cf != null){
			if(cf.isChoose()){
				criteria.addOrder(Order.asc("release"));
				criteria.addOrder(Order.asc(Certification.SHOP));
				criteria.addOrder(Order.desc(Certification.DEPARTDATE));
				criteria.addOrder(Order.desc("createdAt"));
				buildChooseCriteria(criteria, cf);
			}else{
				criteria.addOrder(Order.asc("brand"));
				criteria.addOrder(Order.asc("financial"));
				criteria.addOrder(Order.desc("warehouseDate"));
				buildFindPageCriteria(criteria, cf);
			}
		}else{
			certificationDao.addShopCriteria(criteria,false,null);
		}
		
		return this.findPage(criteria, pageNo, pageSize); 
	}
	
	public Page<Certification> fianPageChiss(CertificationDTO cf,int pageNo, int pageSize,Long[] ids,boolean m){
		Criteria criteria=getEntityDao().createCriteria();
		//车架号
		if(CommonUtil.isNotEmpty(cf.getChassis()))
			criteria.add(Restrictions.like("chassis", cf.getChassis().trim(), MatchMode.ANYWHERE));
		//金融机构ss
		if(cf.getFinancial()!=null&&cf.getFinancial().getId()!=null)
			criteria.createCriteria("financial").add(Restrictions.eq("id", cf.getFinancial().getId()));
		//主机厂
		if(cf.getBrand()!=null&&cf.getBrand().getId()!=null){
			criteria.createCriteria("brand").add(Restrictions.eq("id", cf.getBrand().getId()));
		}
		//店名
		if(cf.getShop()!=null&&cf.getShop().getId()!=null){
			Criteria cri=criteria.createCriteria("shop");
			cri.add(Restrictions.eq("id", cf.getShop().getId()));
			certificationDao.addShopCriteria(cri,true,null);
		}else{
			certificationDao.addShopCriteria(criteria,false,null);
		}
		//状态
		if(cf.getState()!=null&&cf.getState()>=0)
			criteria.add(Restrictions.eq("state", CertificationType.index(cf.getState())));
		if(m)
			criteria.add(Restrictions.not(Restrictions.in("id", ids)));
		return this.findPage(criteria, pageNo, pageSize); 
	}
	public List<Certification> listCertifications(CertificationDTO cf,Long[] id,boolean bo){
		Criteria criteria=getEntityDao().createCriteria();
		 
				//金融机构
				if(cf.getFinancial()!=null&&cf.getFinancial().getId()!=null)
					criteria.createCriteria("financial").add(Restrictions.eq("id", cf.getFinancial().getId()));
				//主机厂
				if(cf.getBrand()!=null&&cf.getBrand().getId()!=null){
					criteria.createCriteria("brand").add(Restrictions.eq("id", cf.getBrand().getId()));
				}
				//店名
				if(cf.getShop()!=null&&cf.getShop().getId()!=null){
					Criteria cri=criteria.createCriteria("shop");
					cri.add(Restrictions.eq("id", cf.getShop().getId()));
					certificationDao.addShopCriteria(cri,true,null);
				}else{
					certificationDao.addShopCriteria(criteria,false,null);
				}
				//状态
				if(cf.getState()!=null&&cf.getState()>=0)
					criteria.add(Restrictions.eq("state", CertificationType.index(cf.getState())));
				if(bo)
					criteria.add(Restrictions.in("id", id));
		return criteria.list();
	}
	
	/**
	 * 查询单个店铺下的合格证
	 * @param shopId 店铺ID
	 * @param pageNo
	 * @param pageSize
	 * @return
	 */
	public Page<Certification> findPage(Long shopId,int pageNo, int pageSize){
		Criteria criteria = getEntityDao().createCriteria();
		criteria.addOrder(Order.asc("brand"));
		criteria.addOrder(Order.asc("financial"));
		criteria.addOrder(Order.desc("warehouseDate"));
		criteria.add(Restrictions.eq("shop.id", shopId));
		criteria.add(Restrictions.eq("state", CertificationType.INSTOCK));
		return super.findPage(criteria, pageNo, pageSize); 
		
	}
	
	/**
	 * 查询单个店铺下的合格证
	 * @param shopId 店铺ID
	 * @return
	 */
	public List<Certification> findList(Long shopId){
		
		Criteria criteria=getEntityDao().createCriteria();
		criteria.createCriteria("shop").add(Restrictions.eq("id", shopId));
		criteria.add(Restrictions.eq("state", CertificationType.INSTOCK));
		return criteria.list(); 
		
	}
	
	/**
	 * 查询单个释放单下的合格证
	 * @param shopId 店铺ID
	 * @return
	 */
	public List<Certification> findList(Long releaseId,String certificationNum,String chassis ){
		
		Criteria criteria=getEntityDao().createCriteria();
		criteria.createCriteria("release").add(Restrictions.eq("id", releaseId));
		if(CommonUtil.isNotEmpty(certificationNum))
			criteria.add(Restrictions.like("certificationNum", certificationNum, MatchMode.ANYWHERE));
		if(CommonUtil.isNotEmpty(chassis))
			criteria.add(Restrictions.like("chassis", chassis, MatchMode.ANYWHERE));
		return criteria.list(); 
		
	}
	
	/**
	 * 查询发车日期在指定月份之内的合格证
	 * @author xuxiaolong
	 * @param finId 金融机构ID
	 * @param shopId店铺ID
	 * @param month 月份 yyyy-MM
	 * @return 个数
	 */
	public int findCertificationInMonth(Long finId,Long shopId,String month){
		Date d=DateUtil.parseDate(month, "yyyy-MM");
		Criteria criteria=getEntityDao().createCriteria();
		criteria.createCriteria("shop").add(Restrictions.eq("id", shopId));
		criteria.createCriteria("financial").add(Restrictions.eq("id", finId));
		criteria.add(Restrictions.ge("warehouseDate", DateUtil.getFirstdayLastdayMonthOne(d)));
		criteria.add(Restrictions.le("warehouseDate", DateUtil.getFirstdayLastdayMonth(d)));
		criteria.add(Restrictions.ne("cmImporting", CopyOfCertificationImporting.RENEW));
		List list=criteria.list();
		if(!CommonUtil.isEmptyList(list))
			return list.size();
		return 0; 
		
	}
	/**
	 * 查询释放日期在指定月份之内的合格证
	 * @param finId 金融机构ID
	 * @param shopId店铺ID
	 * @param month 月份 yyyy-MM
	 * @return 个数
	 */
	public int findReleaseCertificationInMonth(Long finId,Long shopId,String month){
		Date d=DateUtil.parseDate(month, "yyyy-MM");
		if(shopId == 1){
			System.out.println("");
		}
		
		Criteria criteria=getEntityDao().createCriteria();
		criteria.setProjection(Projections.rowCount());
		
		criteria.createCriteria("shop").add(Restrictions.eq("id", shopId));
		criteria.createCriteria("financial").add(Restrictions.eq("id", finId));
		criteria.add(Restrictions.ge("leaveDate", DateUtil.getFirstdayLastdayMonthOne(d)));
		criteria.add(Restrictions.le("leaveDate", DateUtil.getFirstdayLastdayMonth(d)));
		criteria.add(Restrictions.eq("state", CertificationType.RELEASEED));
		
		return ((Number) criteria.uniqueResult()).intValue();
	}
	
	public void buildFindPageCriteria(Criteria criteria,CertificationDTO cf){
		criteria.addOrder(Order.asc(Certification.ID));
		if(cf == null){
			return;
		}
		if(cf.getChange())
			criteria.addOrder(Order.desc("change"));
		//金融机构
		if(cf.getFinancial()!=null&&cf.getFinancial().getId()!=null)
			criteria.createCriteria("financial").add(Restrictions.eq("id", cf.getFinancial().getId()));
		//主机厂
		if(cf.getBrand()!=null&&cf.getBrand().getId()!=null){
			criteria.createCriteria("brand").add(Restrictions.eq("id", cf.getBrand().getId()));
		}
		//店名/店号
		if(cf.getShop()!=null&&(CommonUtil.isNotEmpty(cf.getShop().getShopName())||CommonUtil.isNotEmpty(cf.getShop().getShopMark()))||cf.getAreaUserId()!=null||cf.getShopUserId()!=null){
			Criteria cri=criteria.createCriteria("shop");
			if(cf.getShop() != null && CommonUtil.isNotEmpty(cf.getShop().getShopName()))
				cri.add(Restrictions.like("shopName", cf.getShop().getShopName(),MatchMode.ANYWHERE));
			if(cf.getShop() != null && CommonUtil.isNotEmpty(cf.getShop().getShopMark()))
				cri.add(Restrictions.like("shopMark", cf.getShop().getShopMark(),MatchMode.ANYWHERE));
			User shoper=null,manager=null;
			if(cf.getShopUserId()!=null)
				shoper=userDao.find(cf.getShopUserId());
			if(cf.getAreaUserId()!=null)
				manager=userDao.find(cf.getAreaUserId());
			if(shoper!=null){
				Long ary[]=theNetworkDao.getShops(shoper);
				if(ary!=null){
					cri.add(Restrictions.in("id",ary));
				}else {
					criteria.add(Restrictions.sqlRestriction("1 != 1"));
				}
			}
			if(manager!=null){
				Long ary[]=theNetworkDao.getShops(manager);
				if(ary!=null){
					cri.add(Restrictions.in("id",ary));
				}else {
					criteria.add(Restrictions.sqlRestriction("1 != 1"));
				}
			}
			certificationDao.addShopCriteria(cri,true,null);
		}else{
			certificationDao.addShopCriteria(criteria,false,null);
		}
		//合格证号
		if(CommonUtil.isNotEmpty(cf.getCertificationNum()))
			criteria.add(Restrictions.like("certificationNum", cf.getCertificationNum(),MatchMode.ANYWHERE));
		//车架号
		if(CommonUtil.isNotEmpty(cf.getChassis()))
			criteria.add(Restrictions.like("chassis", cf.getChassis(),MatchMode.ANYWHERE));
		//导入批次号
		if(CommonUtil.isNotEmpty(cf.getBatchNumber()))
			criteria.add(Restrictions.like("batchNumber", cf.getBatchNumber(),MatchMode.ANYWHERE));
		//释放单号
		if(CommonUtil.isNotEmpty(cf.getReleaseNum()))
			criteria.add(Restrictions.like("releaseNum", cf.getReleaseNum(),MatchMode.ANYWHERE));
		//状态
		if(cf.getState()!=null&&cf.getState()>=0)
			criteria.add(Restrictions.eq("state", CertificationType.index(cf.getState())));
		//入库日期
		if(cf.getWarehouseDateBegin()!=null)
			criteria.add(Restrictions.ge("warehouseDate", cf.getWarehouseDateBegin()));
		if(cf.getWarehouseDateEnd()!=null)
			criteria.add(Restrictions.le("warehouseDate", DateUtil.getEndDate(cf.getWarehouseDateEnd())));
		// 出门日
		if(cf.getGoingOutDateBegin()!=null)
			criteria.add(Restrictions.ge("goingOutDate", cf.getGoingOutDateBegin()));
		if(cf.getGoingOutDateEnd()!=null)
			criteria.add(Restrictions.le("goingOutDate", DateUtil.getEndDate(cf.getGoingOutDateEnd())));
		
		//预计到店日
		if(cf.getScheduledDateBegin()!=null)
			criteria.add(Restrictions.ge("scheduledDate", cf.getScheduledDateBegin()));
		if(cf.getScheduledDateEnd()!=null)
			criteria.add(Restrictions.le("scheduledDate", DateUtil.getEndDate(cf.getScheduledDateEnd())));
		
		
		//出库时间
		if(cf.getLeaveDateBegin()!=null)
			criteria.add(Restrictions.ge("leaveDate", cf.getLeaveDateBegin()));
		if(cf.getLeaveDateEnd()!=null)
			criteria.add(Restrictions.le("leaveDate", DateUtil.getEndDate(cf.getLeaveDateEnd())));
		
		//申请出库日期
		if(cf.getApplyReleaseDateBegin()!=null)
			criteria.add(Restrictions.ge("applyReleaseDate", cf.getApplyReleaseDateBegin()));
		if(cf.getApplyReleaseDateEnd()!=null)
			criteria.add(Restrictions.le("applyReleaseDate", DateUtil.getEndDate(cf.getApplyReleaseDateEnd())));
		
		//汇票号开始时间
		if(cf.getDraftBeginDateBegin()!=null)
			criteria.add(Restrictions.ge("draftBeginDate", cf.getDraftBeginDateBegin()));
		if(cf.getDraftBeginDateEnd()!=null)
			criteria.add(Restrictions.le("draftBeginDate", DateUtil.getEndDate(cf.getDraftBeginDateEnd())));

		//汇票号结束时间
		if(cf.getDraftEndDateBegin()!=null)
			criteria.add(Restrictions.ge("draftEndDate", cf.getDraftEndDateBegin()));
		if(cf.getDraftEndDateEnd()!=null)
			criteria.add(Restrictions.le("draftEndDate", DateUtil.getEndDate(cf.getDraftEndDateEnd())));
		
		//只查可以释放的
		if(cf.getReleaseOnly()!=null&&cf.getReleaseOnly()==1){
			criteria.add(Restrictions.eq("state", CertificationType.INSTOCK));
			Object ary[]=new Object[]{ReleaseState.FINISH,ReleaseState.PART_PASSED};
			criteria.createCriteria("release").add(Restrictions.in("state", ary));
		}
		//释放人
		if(cf.getReleaseUserId()!=null){
			criteria.createCriteria("releaseUser").add(Restrictions.eq("id", cf.getReleaseUserId()));
		}
	}
	
	public void buildChooseCriteria(Criteria criteria,CertificationDTO cf){
		
		//车架号
		if(CommonUtil.isNotEmpty(cf.getChassis()))
			criteria.add(Restrictions.like("chassis", cf.getChassis().trim(), MatchMode.ANYWHERE));
		//金融机构
		if(cf.getFinancial()!=null&&cf.getFinancial().getId()!=null)
			criteria.createCriteria("financial").add(Restrictions.eq("id", cf.getFinancial().getId()));
		//主机厂
		if(cf.getBrand()!=null&&cf.getBrand().getId()!=null){
			criteria.createCriteria("brand").add(Restrictions.eq("id", cf.getBrand().getId()));
		}
		//店名
		if(cf.getShop()!=null&&cf.getShop().getId()!=null){
			Criteria cri=criteria.createCriteria("shop");
			cri.add(Restrictions.eq("id", cf.getShop().getId()));
			certificationDao.addShopCriteria(cri,true,null);
		}else{
			certificationDao.addShopCriteria(criteria,false,null);
		}
		//状态
		if(cf.getState()!=null&&cf.getState()>=0){
			if(CommonUtil.isNotEmpty(cf.getType())&&cf.getType().equals("release")){
				criteria.add(Restrictions.or(Restrictions.eq("state", CertificationType.index(cf.getState())), Restrictions.and(Restrictions.eq("state", CertificationType.ONORDER), Restrictions.eq("change",true))));
			}else{
				criteria.add(Restrictions.eq("state", CertificationType.index(cf.getState())));
			}
		}
		//只要没有关联释放单的
		/*if(cf.getReleaseId()==null)
			criteria.add(Restrictions.isNull("release"));
		else
			criteria.add(Restrictions.or(Restrictions.isNull("release"),Restrictions.eq("release", releaseDao.find(cf.getReleaseId()))));*/
		
		
	}
	public Integer batchNumber(){
		SimpleDateFormat dft = new SimpleDateFormat("yyyy-MM-dd");	
		Date beginDate = new Date();	
		Calendar date = Calendar.getInstance();
		date.setTime(beginDate);	
		date.set(Calendar.DATE, date.get(Calendar.DATE) - 1);	
		Date endDate=new Date();
		try {
			endDate = dft.parse(dft.format(date.getTime()));
		} catch (ParseException e) {
			e.printStackTrace();
		}
		Criteria criteria = certificationDao.createCriteria();
		criteria.add(Restrictions.between("createDate", endDate, new Date()));
		criteria.add(Restrictions.eq("cmImporting", CopyOfCertificationImporting.YES));
		List<Certification> list =criteria.list();
		Integer aInteger=list.size();
		return aInteger;
	}
	
	public Integer getcreInet(Long shopid){
		Criteria criteria = certificationDao.createCriteria();
		criteria.setProjection(Projections.rowCount());
		criteria.add(Restrictions.eq("state",  CertificationType.INSTOCK));
		criteria.add(Restrictions.eq("shop.id",shopid));
		
		return ((Number) criteria.uniqueResult()).intValue();
	}

	/**
	 * 单个合格证释放(弃用)
	 * @author xuxiaolong
	 * @param id
	 */
	/*public void release(Long id){
		Certification cf= this.find(id);
		if(cf!=null){
			cf.setState(CertificationType.RELEASEED);
			cf.setLeaveDate(new Date());
			this.save(cf);
			Release rs=cf.getRelease();
			List<Certification> list=rs.getList();
			boolean over=true;
			//如果所有的合格证都已释放,将释放单状态改为已完成
			for(Certification tmp:list ){
				if(tmp.getState().compareTo(CertificationType.RELEASEED)!=0){
					over=false;
					break;
				}
			}
			if(over){
				rs.setState(ReleaseState.FINISH);
				rs.setReleaseDate(new Date());
				Workflow wf = wfService.find(WorkflowType.RELEASE,cf.getRelease().getId());
				String userName = ThreadLocalHolder.get().toString();
				User user =userDao.getUserByName(userName);
				wfService.audit(user, wf,WorkflowState.FINISH, WorkflowOperateType.FINISH, "结束释放流程");
				rsDao.save(rs);
			}
		}
	}*/
	
	@SuppressWarnings("unchecked")
	public List<Certification> findCfs(CertificationDTO search) {
		Criteria criteria = certificationDao.createCriteria();
		if(search != null){
			if(search.isChoose()){
				criteria.addOrder(Order.asc("release"));
				criteria.addOrder(Order.asc(Certification.SHOP));
				criteria.addOrder(Order.desc(Certification.DEPARTDATE));
				criteria.addOrder(Order.desc("createdAt"));
				buildChooseCriteria(criteria, search);
			}else{
				criteria.addOrder(Order.asc("brand"));
				criteria.addOrder(Order.asc("financial"));
				criteria.addOrder(Order.desc("warehouseDate"));
				buildFindPageCriteria(criteria, search);
			}
		}else{
			certificationDao.addShopCriteria(criteria,false,null);
		}
//		this.buildFindPageCriteria(criteria, search);
		//添加权限过滤
//		certificationDao.addShopCriteria(criteria,false,null);
		
		return criteria.list();
	}

	/**
	 * @param shopId
	 * @return
	 */
	public List<Certification> findCfByShopId(Long shopId) {
		Criteria criteria = this.createCriteriaCfByShop(shopId);
		return criteria.list(); 
	}
	
	//根据合格证号查询合格证
	public Certification findCfByCerNum(String cerNum) {
		List<Certification> cers=certificationDao.findBy("certificationNum", cerNum);
		if(!CommonUtil.isEmptyList(cers))
			return cers.get(0);
		return null; 
	}
	
	//根据指定属性查询合格证
	public Certification findByProperty(String propertyName,Object value) {
		List<Certification> cers=certificationDao.findBy(propertyName, value);
		if(!CommonUtil.isEmptyList(cers))
			return cers.get(0);
		return null; 
	}

	/**
	 * @param shopId
	 * @return
	 */
	private Criteria createCriteriaCfByShop(Long shopId) {
		Criteria criteria = getEntityDao().createCriteria();
		criteria.addOrder(Order.desc("change"));
		criteria.addOrder(Order.desc(Certification.DEPARTDATE));
		criteria.add(Restrictions.eq("shop.id", shopId));
		criteria.add(Restrictions.eq("state", CertificationType.INSTOCK));
		return criteria;
	}
	/**
	 * 查询在库的合格证
	 * @param havaShops
	 * @return
	 */
	public List<Shop> instockCertification(Set<Shop> havaShops){
		Criteria criteria = getEntityDao().createCriteria();
		criteria.setProjection(Projections.groupProperty("shop"));
		criteria.add(Restrictions.in("shop", havaShops));
		criteria.add(Restrictions.eq("state", CertificationType.INSTOCK));
		return criteria.list();
	}
	/**
	 * 合格证补录待审核数量
	 * @param shops
	 * @return
	 */
	public Integer waitverify(Set<Shop> shops){
		Criteria criteria = getEntityDao().createCriteria();
		criteria.setProjection(Projections.rowCount());
		
		criteria.add(Restrictions.in("shop", shops));
		criteria.add(Restrictions.eq("state", CertificationType.APPROVED));
		
		return ((Number)criteria.uniqueResult()).intValue();
	}
	/**
	 * 查询在途需要收证的--超过收证日
	 * @param cer
	 * @return
	 */
	public List<Prewarning> prewarningList(boolean boo,int ki){
		List<Prewarning> prewarnings = new ArrayList<Prewarning>();
		SimpleDateFormat dft = new SimpleDateFormat("yyyy-MM-dd");
		Criteria criteria = getEntityDao().createCriteria();
		if(ki == 0){//预计到店日
			if(boo){//没有超过
				criteria.add(Restrictions.between("scheduledDate",new Date(), DateUtil.addDays(new Date(), 7)));
			}else{//超过
//				ProjectionList projectionList = Projections.projectionList();
//				projectionList.add(Projections.groupProperty("brand"));
////				 
//				projectionList.add(Projections.groupProperty("shop"));
//				 
//				projectionList.add(Projections.rowCount(), "cfCount");
//
//				criteria.setProjection(projectionList);
				criteria.add(Restrictions.lt("scheduledDate", new Date()));
			}
			criteria.add(Restrictions.eq("state", CertificationType.ONORDER));
		}else{//货款到期日
			if(boo){//没有超过
				criteria.add(Restrictions.between("draftEndDate",new Date(), DateUtil.addDays(new Date(), 7)));
			}else{//超过
				criteria.add(Restrictions.lt("draftEndDate", new Date()));
			}
			criteria.add(Restrictions.eq("state", CertificationType.INSTOCK));
		}
		certificationDao.addShopCriteria(criteria, false, null);//添加数据过滤
		List<Certification> certifications = criteria.list();
		for(int i = 0;i < certifications.size();i++){
			String msg ="";
			Certification certification = certifications.get(i);
			Prewarning prewarning = new Prewarning();
			
			if(ki == 0){
				if(boo){//没有超过
					prewarning.setBool(false);
					msg =certification.getShop().getShopName()+"  "+certification.getChassis()+"  预计："+dft.format(certification.getScheduledDate())+"  到店";
				}else{//超过
					prewarning.setBool(true);
					msg = certification.getBrand().getName()+"    "+certification.getShop().getShopName()+"  的融资车已超过预计到店日";
				}
			prewarning.setPrwarningtype(Prwarningtype.ARRIVE);
			}else{
				if(boo){//没有超过
					prewarning.setBool(false);
					msg = certification.getShop().getShopName()+"   "+certification.getDraft()+"  的贷款"+dft.format(certification.getDraftEndDate())+"到期";
				}else{//超过
					prewarning.setBool(true);
					msg = certification.getShop().getShopName()+"   "+certification.getDraft()+"  的贷款 已到期";
				}
			prewarning.setPrwarningtype(Prwarningtype.DRAFT);
			}
			
			
			prewarning.setMsg(msg);
			prewarning.setUrlAddress("cm/index.do");
			prewarnings.add(prewarning);
		}
		return prewarnings;
	}
	
	public boolean getCertifition(Certificate cer){
		return certificationDao.getCertifition(cer);
	}
	
	/**
	 * 融资车总台数合计同比 
	 * @author xuxiaolong
	 * @param startDate
	 * @param endDate
	 * @return
	 */
	public Map<Integer,Integer> findCarTotal(Date startDate,Date endDate){
		
		return getEntityDao().findCarTotal(startDate, endDate);
	}
	
	/**
	 * 各银行融资车台数
	 * @author xuxiaolong
	 * @param startDate
	 * @param endDate
	 * @return
	 */
	public List<Object[]> findCarFin(Date startDate,Date endDate){
		
		return getEntityDao().findCarFin(startDate, endDate);
	}

	
	/**
	 * 各银行融资车台数分布
	 * @author xuxiaolong
	 * @param startDate
	 * @param endDate
	 * @return
	 */
	public Map<String,Integer> findCarYear(Date startDate,Date endDate){
		
		return getEntityDao().findCarYear(startDate, endDate);
	}
	
	/**
	 * 上月结余数
	 * 
	 * @param shopId
	 * @param monthDate
	 * @return
	 */
	public int findBeforeCertifcatiionInQty(Long shopId, String monthDate) {
		Date d = DateUtil.parseDate(monthDate, "yyyy-MM");
		d = DateUtil.getFirstdayLastdayMonth(DateUtil.addMonths(d, -1));
		Criteria criteria = getEntityDao().createCriteria();
		criteria.setProjection(Projections.projectionList().add(Projections.count("id")));
		criteria.createCriteria("shop").add(Restrictions.eq("id", shopId));
		criteria.add(Restrictions.eq("state", CertificationType.INSTOCK));
//		criteria.createCriteria("financial").add(Restrictions.eq("id", finId));
//		criteria.add(Restrictions.ge("warehouseDate", DateUtil.getFirstdayLastdayMonthOne(d)));
		criteria.add(Restrictions.le("warehouseDate", DateUtil.getFirstdayLastdayMonth(d)));
		criteria.add(Restrictions.ne("cmImporting", CopyOfCertificationImporting.RENEW));
		
		Object object = criteria.uniqueResult();;
		
		if(object != null){
			return Integer.valueOf(object.toString());
		}
			
		return 0; 
	}
	
	
	/**
	 * 查询店铺这月入库车数量，不包括换证入库
	 * 
	 * @param shopId
	 * @param month
	 * @return
	 */
	public int findCertificationInMonth(Long shopId, String month){
		Date d = DateUtil.parseDate(month, "yyyy-MM");
		Criteria criteria = getEntityDao().createCriteria();
		criteria.setProjection(Projections.projectionList().add(Projections.count("id")));
		criteria.createCriteria("shop").add(Restrictions.eq("id", shopId));
//		criteria.createCriteria("financial").add(Restrictions.eq("id", finId));
		criteria.add(Restrictions.ge("warehouseDate", DateUtil.getFirstdayLastdayMonthOne(d)));
		criteria.add(Restrictions.le("warehouseDate", DateUtil.getFirstdayLastdayMonth(d)));
		criteria.add(Restrictions.ne("cmImporting", CopyOfCertificationImporting.RENEW));
		
		Object object = criteria.uniqueResult();;
		
		if(object != null){
			return Integer.valueOf(object.toString());
		}
			
		return 0; 
		
	}

	public int findCertificationInMonth(Long shopId, String monthDate,
			Set<Financial> fins) {
		Date d = DateUtil.parseDate(monthDate, "yyyy-MM");
		Criteria criteria = getEntityDao().createCriteria();
		criteria.setProjection(Projections.projectionList().add(Projections.count("id")));
		criteria.createCriteria("shop").add(Restrictions.eq("id", shopId));
		
		if(fins.size() > 0 ){
			criteria.add(Restrictions.in("financial", fins));
		}else{
			criteria.add(Restrictions.sqlRestriction("1 != 1"));
		}
//		criteria.createCriteria("financial").add(Restrictions.eq("id", finId));
		criteria.add(Restrictions.ge("warehouseDate", DateUtil.getFirstdayLastdayMonthOne(d)));
		criteria.add(Restrictions.le("warehouseDate", DateUtil.getFirstdayLastdayMonth(d)));
		criteria.add(Restrictions.ne("cmImporting", CopyOfCertificationImporting.RENEW));
		
		Object object = criteria.uniqueResult();;
		
		if(object != null){
			return Integer.valueOf(object.toString());
		}
			
		return 0; 
	}
/**
 * 用于统计发票中的本月入库
 * @param shopId
 * @param monthDate
 * @param idFinancial
 * @return
 */
	public int findCertificationInMonthBill(Long shopId,Long idFinancial, String monthDate) {
		
		Date d = DateUtil.parseDate(monthDate, "yyyy-MM");
		if(shopId == 7){
			System.out.println(DateUtil.formatDate(DateUtil.getFirstdayLastdayMonthOne(d))+"    "+DateUtil.formatDate(DateUtil.getFirstdayLastdayMonth(d)));
		}
		Criteria criteria = getEntityDao().createCriteria();
		criteria.setProjection(Projections.projectionList().add(Projections.count("id")));
		criteria.createCriteria("shop").add(Restrictions.eq("id", shopId));
		criteria.createCriteria("financial").add(Restrictions.eq("id", idFinancial));
//		criteria.createCriteria("financial").add(Restrictions.eq("id", finId));
		criteria.add(Restrictions.ge("warehouseDate", DateUtil.getFirstdayLastdayMonthOne(d)));
		criteria.add(Restrictions.le("warehouseDate", DateUtil.getFirstdayLastdayMonth(d)));
		criteria.add(Restrictions.ne("cmImporting", CopyOfCertificationImporting.RENEW));
		
		Object object = criteria.uniqueResult();;
		
		if(object != null){
			return Integer.valueOf(object.toString());
		}
			
		return 0; 
	}
	/**
	 * 用于统计发票中的当月结存
	 * @param shopId
	 * @param monthDate
	 * @param idFinancial
	 * @return
	 */
	public int finCertifcatiionInQtyBill(Long shopId, Long finalId, String monthDate) {
		Date d = DateUtil.parseDate(monthDate, "yyyy-MM");
//		d = DateUtil.getFirstdayLastdayMonth(DateUtil.addMonths(d, -1));
		Criteria criteria = getEntityDao().createCriteria();
		criteria.setProjection(Projections.projectionList().add(Projections.count("id")));
		criteria.createCriteria("shop").add(Restrictions.eq("id", shopId));
		criteria.createCriteria("financial").add(Restrictions.eq("id", finalId));
		
		criteria.add(Restrictions.le("warehouseDate", DateUtil.getFirstdayLastdayMonth(d)));
		criteria.add(Restrictions.or(Restrictions.eq("state", CertificationType.INSTOCK), Restrictions.and(Restrictions.eq("state", CertificationType.RELEASEED), Restrictions.gt("leaveDate", DateUtil.getFirstdayLastdayMonth(d)))));//当月在库或者已释放的合格证
//		criteria.createCriteria("financial").add(Restrictions.eq("id", finId));
//		criteria.add(Restrictions.ge("warehouseDate", DateUtil.getFirstdayLastdayMonthOne(d)));
		criteria.add(Restrictions.ne("cmImporting", CopyOfCertificationImporting.RENEW));
		
		Object object = criteria.uniqueResult();;
		
		if(object != null){
			return Integer.valueOf(object.toString());
		}
			
		return 0; 
	}
	/**
	 * 用于统计发票中的上月结存
	 * @param shopId
	 * @param monthDate
	 * @param idFinancial
	 * @return
	 */
	public int findBeforeCertifcatiionInQtyBill(Long shopId, Long finalId, String monthDate) {
		Date d = DateUtil.parseDate(monthDate, "yyyy-MM");
		d = DateUtil.getFirstdayLastdayMonth(DateUtil.addMonths(d, -1));
		Criteria criteria = getEntityDao().createCriteria();
		criteria.setProjection(Projections.projectionList().add(Projections.count("id")));
		criteria.createCriteria("shop").add(Restrictions.eq("id", shopId));
		criteria.createCriteria("financial").add(Restrictions.eq("id", finalId));
		
		criteria.add(Restrictions.le("warehouseDate", DateUtil.getFirstdayLastdayMonth(d)));
		criteria.add(Restrictions.or(Restrictions.eq("state", CertificationType.INSTOCK), Restrictions.and(Restrictions.eq("state", CertificationType.RELEASEED), Restrictions.gt("leaveDate",DateUtil.getFirstdayLastdayMonth(d)))));//当月在库或者已释放的合格证
//		criteria.createCriteria("financial").add(Restrictions.eq("id", finId));
//		criteria.add(Restrictions.ge("warehouseDate", DateUtil.getFirstdayLastdayMonthOne(d)));
		criteria.add(Restrictions.ne("cmImporting", CopyOfCertificationImporting.RENEW));
		
		Object object = criteria.uniqueResult();;
		
		if(object != null){
			return Integer.valueOf(object.toString());
		}
			
		return 0; 
	}
	 
	public int findBeforeCertifcatiionInQty(Long shopId, String monthDate,
			Set<Financial> fins) {
		Date d = DateUtil.parseDate(monthDate, "yyyy-MM");
		d = DateUtil.getFirstdayLastdayMonth(DateUtil.addMonths(d, -1));
		Criteria criteria = getEntityDao().createCriteria();
		criteria.setProjection(Projections.projectionList().add(Projections.count("id")));
		criteria.createCriteria("shop").add(Restrictions.eq("id", shopId));
		
		if(fins.size() > 0 ){
			criteria.add(Restrictions.in("financial", fins));
		}else{
			criteria.add(Restrictions.sqlRestriction("1 != 1"));
		}
		
		criteria.add(Restrictions.eq("state", CertificationType.INSTOCK));
//		criteria.createCriteria("financial").add(Restrictions.eq("id", finId));
//		criteria.add(Restrictions.ge("warehouseDate", DateUtil.getFirstdayLastdayMonthOne(d)));
		criteria.add(Restrictions.le("warehouseDate", DateUtil.getFirstdayLastdayMonth(d)));
		criteria.add(Restrictions.ne("cmImporting", CopyOfCertificationImporting.RENEW));
		
		Object object = criteria.uniqueResult();;
		
		if(object != null){
			return Integer.valueOf(object.toString());
		}
			
		return 0; 
	}
	/**
	 * 查询在途需要收证的--超过收证日
	 * @param cer
	 * @return
	 */
	public List<Prewarning> prewarningList(){
		List<Prewarning> prewarnings = new ArrayList<Prewarning>();
		SimpleDateFormat dft = new SimpleDateFormat("yyyy-MM-dd");
		Criteria criteria = getEntityDao().createCriteria();
		ProjectionList projectionList = Projections.projectionList();
		projectionList.add(Projections.groupProperty("brand"));
//		 
		projectionList.add(Projections.groupProperty("shop"));
		 
		projectionList.add(Projections.rowCount(), "cfCount");

		criteria.setProjection(projectionList);
		criteria.add(Restrictions.lt("scheduledDate", new Date()));
		criteria.add(Restrictions.eq("state", CertificationType.ONORDER));
		certificationDao.addShopCriteria(criteria,false,null);//添加数据过滤
		List<Object> obejcts = criteria.list();
		for(int i=0 ; i<obejcts.size();i++){
			Object[] ob = (Object[]) obejcts.get(i);
			Shop shop = (Shop) ob[1];
			Brand brand = (Brand) ob[0];
			Long tai = (Long) ob[2];
			Prewarning prewarning = new Prewarning();
			prewarning.setPrwarningtype(Prwarningtype.DRAFT);
			prewarning.setBool(true);
			prewarning.setMsg(shop.getShopName()+"  共("+tai+")台   融资车已超过预计到店日");
			prewarning.setUrlAddress("cm/index.do");
			prewarnings.add(prewarning);
		}
		return prewarnings;
	}
	/**
	 * 当天释放数
	 * @param shopSet
	 * @return
	 */
	public int releaseCertifiation(Set<Shop> shopSet){
		Criteria criteria = getEntityDao().createCriteria();
		criteria.setProjection(Projections.rowCount());
		criteria.add(Restrictions.in("shop", shopSet));
		criteria.add(Restrictions.between("leaveDate", DateUtil.getDate(), new Date()));
		criteria.add(Restrictions.eq("state", CertificationType.RELEASEED));
		
		return ((Number)criteria.uniqueResult()).intValue();
		
	}
	
	public List<Object> thePrewarnings(Long[] ids){
		Long[] theIdLongs = theNetworkDao.cerIds(ids);
		Criteria criteria = getEntityDao().createCriteria();
		criteria.setProjection(Projections.projectionList().add(Projections.alias(Projections.groupProperty(Certification.SHOP),"shop"))
				.add(Projections.alias(Projections.groupProperty("financial"),"financial"))
				.add(Projections.alias(Projections.rowCount(), "cfCount")));
		criteria.createCriteria("shop").add(Restrictions.in("id", ids));
		criteria.add(Restrictions.in("id", theIdLongs));
//		criteria.add(Restrictions.ne("state", CertificationType.RELEASEED));
		criteria.add(Restrictions.eq("state", CertificationType.INSTOCK));
		criteria.add(Restrictions.isNotNull("theAddress"));
		criteria.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
		List<Object> certifications = criteria.list();
		 
		return certifications;
	}
	
	
	/**
	 * 统计各个店各个主机的本月入库
	 * @param shopId
	 * @param monthDate
	 * @param financialId
	 * @param shops 
	 * @return
	 */
	public List<Object[]> findCountCurrentMonth(Long financialId, String monthDate, List<Shop> shops) {
			
		Date d = DateUtil.parseDate(monthDate, "yyyy-MM");
	
		Criteria criteria = getEntityDao().createCriteria();
		criteria.setProjection(Projections.projectionList().add(Projections.count("id")).
				add(Projections.groupProperty("shop")).add(Projections.groupProperty("brand")));
		criteria.createCriteria("financial").add(Restrictions.eq("id", financialId));
		criteria.add(Restrictions.ge("warehouseDate", DateUtil.getFirstdayLastdayMonthOne(d)));
		criteria.add(Restrictions.le("warehouseDate", DateUtil.getFirstdayLastdayMonth(d)));
		criteria.add(Restrictions.ne("cmImporting", CopyOfCertificationImporting.RENEW));
		criteria.add(Restrictions.in("shop", shops));
		
		return criteria.list(); 
	}
		
	/**
	 * 统计各个店各个主机的上月结存
	 * @param shopId
	 * @param monthDate
	 * @param shops 
	 * @param idFinancial
	 * @return
	 */
	public List<Object[]> findCountPreMonth(Long financialId, String monthDate, List<Shop> shops) {
		Date d = DateUtil.parseDate(monthDate, "yyyy-MM");
		d = DateUtil.getFirstdayLastdayMonth(DateUtil.addMonths(d, -1));
		Criteria criteria = getEntityDao().createCriteria();
		criteria.setProjection(Projections.projectionList().add(Projections.count("id")).
				add(Projections.groupProperty("shop")).add(Projections.groupProperty("brand")));
		criteria.createCriteria("financial").add(Restrictions.eq("id", financialId));
			
		criteria.add(Restrictions.le("warehouseDate", DateUtil.getFirstdayLastdayMonth(d)));
		criteria.add(Restrictions.or(Restrictions.eq("state", CertificationType.INSTOCK), 
				Restrictions.and(Restrictions.eq("state", CertificationType.RELEASEED), 
						Restrictions.gt("leaveDate",DateUtil.getFirstdayLastdayMonth(d)))));//当月在库或者已释放的合格证
		criteria.add(Restrictions.ne("cmImporting", CopyOfCertificationImporting.RENEW));
		
		criteria.add(Restrictions.in("shop", shops));
				
		return criteria.list(); 
	}

	/**
	 * 删除在库合格证。
	 * 
	 * @param certification
	 */
	public void deleteInStock(Certification certification) {
		//删除补录流程 TODO
		wfService.destory(certification.getId(), WorkflowType.ADD);
		//删除二网关联数据
		List<TheNetworkList> theNetworkLists = theNetworkListDao.findBy("creId", certification.getId());
		if(theNetworkLists != null){
			for (Iterator<TheNetworkList> iterator = theNetworkLists.iterator(); iterator
					.hasNext();) {
				TheNetworkList theNetworkList = iterator
						.next();
				TheNetwork theNetwork = theNetworkList.getTheNetwork();
				if(theNetwork.getTheNetworks() != null && theNetwork.getTheNetworks().size() == 1){
					//只存当前合格证一个合格证的二网申请，删除整个申请和流程
					theNetworkService.destoryAndWorkflow(theNetwork.getId());
				}else{
					theNetworkListDao.destory(theNetworkList);
				}
			}
		}
		
		//删除修改记录关联数据 TODO:
		List<Certificate> certificates = certificateService.getEntityDao().findBy("certification", certification);
		if(certificates != null){
			for (Iterator<Certificate> iterator = certificates.iterator(); iterator
					.hasNext();) {
				Certificate certificate = iterator.next();
//				wfService.destory(certificate.getId(), WorkflowType.UPDATE);
//				certificateService.destory(certificate);
				certificateService.destoryAndWorkFlow(certificate.getId());
			}
		}
		
		//删除释放单
		Release release = certification.getRelease();
		if(release != null){
			Set<Certification> certifications = release.getCertifications();
			if(certifications != null && certifications.size() == 1){
				releaseService.destory(release.getId());
			}else{
				certification.setRelease(null);
				this.save(certification);
			}
		}
		
		//删除盘库
		List<CheckStockOption> checkStockOptions = checkStockOptionService.getEntityDao().findBy("certification", certification);
		if(checkStockOptions != null){
			for (Iterator<CheckStockOption> iterator = checkStockOptions.iterator(); iterator
					.hasNext();) {
				CheckStockOption checkStockOption =  iterator.next();
				CheckStock checkStock = checkStockOption.getCheckStock();
				if(checkStock.getOptions() != null && checkStock.getOptions().size() == 1){
					checkStockService.destory(checkStock.getId());
				}else{
					checkStock.getOptions().remove(checkStockOption);
					checkStockOption.setCertification(null);
					checkStockOptionService.save(checkStockOption);
					checkStockOptionService.destory(checkStockOption);
					checkStockService.save(checkStock);
				}
			}
		}
		
		//删除换证，和换证流程 //TODO:多对多关系，不用删除直接删除合格证会级联删除关系的。需确定。
//		List<CertificateRenew> certificateRenews = certificateRenewService.findByCertification(certification);
		
		//删除批量补录合格证。 //TODO:不需要处理。
		
		//保存删除合格证的记录
		DeleteCertificationRecord deleteRecord = new DeleteCertificationRecord();
		String[] properties = { "shop", "financial", "brand",  "certificationNum", "departDate" 
				, "motorcycleType" , "chassis", "engine" , "orderNumber", "supplyPrice" , "draft", "draftBeginDate" , "draftEndDate", "goingOutDate" , "scheduledDate", "createDate" , "buluDate", "applyReleaseDate" , "warehouseDate", "applicationWarehouseDate",
				"theAddress", "remarks" , "batchNumber", "change" , "state", "cmImporting" , "applyTheDate"};
		SshBeanUtils.copyProperties(certification, deleteRecord, properties);
		deleteCertificationRecordService.save(deleteRecord);
		
		this.destory(certification);
	}		
}
